﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data.SqlClient;
using System.Data;

namespace T3Task5
{
    class DBHelper
    {
        //静态字段
        public static string connStr = "server=.;database=Supermarket;Integrated Security=true ";
        //静态方法一查询
        public static SqlDataReader Select(string sql)
        {
            //1、创建连接对象
            SqlConnection conn = new SqlConnection(connStr);
            //2、打开连接
            conn.Open();
            //3、创建对象命令
            SqlCommand cmd = new SqlCommand(sql, conn);
            //4、ExecuteReader执行
            return cmd.ExecuteReader(System.Data.CommandBehavior.CloseConnection);
        }


        public static DataTable SelectAdapter(string sql)
        {
            //1.创建数据库连接对象
            SqlConnection conn = new SqlConnection(connStr);
            //2.创建数据适配对象
            SqlDataAdapter da = new SqlDataAdapter(sql, conn);
            //3.创建数据集，用于缓存数据
            DataTable dt = new DataTable();
            //4.调用DataAdapter对象的Fill()方法填充
            da.Fill(dt);
            //5.绑定数据源到ListView或DAtaGridView等数据空间上
            return dt;

        }

        //执行查询操作，获取
        public static DataSet GetDataSet(string sqlSet)
        {
            //创建ds（数据集）对象
            DataSet ds = new DataSet();
            //1、创建连接对象
            SqlConnection conn = new SqlConnection(connStr);
            //2、打开连接
            conn.Open();
            //定义查询数据库的SQL语句
            SqlDataAdapter dap = new SqlDataAdapter(sqlSet,conn);
            //填充数据
            dap.Fill(ds);
            //关闭并释放连接对象
            conn.Close();
            return ds;
        }

        //执行查询操作，获取
        /*public static DataTable GetDataTable(string sqlStr)
        {

            return GetDataTable(sqlStr).Tables[0];
        }*/


        //静态方法二增删改
        public static bool GetNonQuery(string sql)
        {
            bool result = false;
            //1、创建连接对象
            SqlConnection conn = new SqlConnection(connStr);
            //2、打开连接
            conn.Open();
            //3、创建对象命令
            SqlCommand cmd = new SqlCommand(sql, conn);
            //4、ExecuteNonQuery执行(增删改)
            int number = cmd.ExecuteNonQuery();
            if (number > 0)
            {
                result = true;
            }
            conn.Close();
            return result;

        }

        //执行集合函数操作
        public static object ExecuteScalar(string sqlStr)
        {
            //1、创建连接对象
            SqlConnection conn = new SqlConnection(connStr);
            //2、打开连接
            conn.Open();
            //3、创建对象命令
            SqlCommand cmd = new SqlCommand(sqlStr,conn);
            object result=cmd.ExecuteScalar();
            conn.Close();
            return result;

        }

        public static DataTable ExeAll(string sqlcmd)
        {
            DataTable dt = null;
            SqlConnection con = new SqlConnection();
                SqlDataAdapter sda = new SqlDataAdapter(sqlcmd, con);
                DataSet ds = new DataSet();
                sda.Fill(ds);
                dt = ds.Tables[0];
            
            return dt;
        }
    }
}

 
